# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html

import sqlite3


class ComicscrapyPipeline(object):
    def open_spider(self, spider):
        db_name = spider.settings.get('SQLITE_DB_NAME', '../db.sqlite3')
        self.db_conn = sqlite3.connect(db_name)
        self.db_cur = self.db_conn.cursor()
        # self.db_cur.execute('CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY AUTOINCREMENT,'
        #                     'name VARCHAR(50),'
        #                     'popularity VARCHAR(50),'
        #                     'description VARCHAR(2000),'
        #                     'book_url VARCHAR(255),'
        #                     'book_img VARCHAR(255))'
        #
        #                     )

    def process_item(self, item, spider):
        book_types = item['book_type']
        book_name = item['book_name']
        book_chapter = item['book_chapter']

#==============================得到book信息，插入comicinfo_comicinfo 数据表==========================
        try:
            self.db_cur.execute(
                "INSERT INTO comicinfo_comicinfo(id,book_name,popularity,description,book_url,book_img,content_url,book_area,book_type) VALUES (?,?,?,?,?,?,?,?,?)",
                (item['book_id'],item['book_name'][0], item['book_popularity'][0], item['book_des'],
                 item['book_url'], item['book_img'],item['content_url'],item['book_area'][0],item['book_type'][0]))
        except sqlite3.IntegrityError as e:
            print('漫画已经存在')
        self.db_conn.commit()

#==========================得到book分类，插入分类数据表===================================================
        try:

                self.db_cur.execute("INSERT INTO comicinfo_comickind(kind_name,kind_desc) VALUES (?,?)",
                                    (item['book_type'][0], item['book_type'][0]))
        except sqlite3.IntegrityError as e:
            print('分类已经存在')

        self.db_conn.commit()

#================== 在comicinfo_comicinfo得到book的id =================================
        sql_info = "select id from comicinfo_comicinfo where book_name='{}'".format(item['book_name'][0])
        self.db_cur.execute(sql_info)
        book_id_list = self.db_cur.fetchall()
        book_id_raw = book_id_list[0]
        book_id = book_id_raw[0]

#=================在comicinfo_comickind得到book分类id===============================

        sql_kind = "select id from comicinfo_comickind where kind_name='{}'".format(item['book_type'][0])
        self.db_cur.execute(sql_kind)
        kind_id_list = self.db_cur.fetchall()
        if len(kind_id_list)!=0:
            kind_id_raw = kind_id_list[-1]
            kind_id = kind_id_raw[0]

# ======================插入数据到中间表comicinfo_comicinfo_kind=====================
            try:
                self.db_cur.execute("INSERT INTO comicinfo_comicinfo_kind(comicinfo_id,comickind_id) VALUES (?,?)",
                                    (book_id, kind_id))
            except sqlite3.IntegrityError as e:
                print('漫画和分类已经关联')

        self.db_conn.commit()

# ===================插入章节表=================
        for chapter in book_chapter:
            try:
                self.db_cur.execute(
                    "INSERT INTO comicinfo_comicindex(id,catalog_title,belongto_comic_id,catalog_url) VALUES (?,?,?,?)",
                    (item['book_id']+'/'+chapter['chapter_id'],chapter['chapter_name'],item['book_id'],chapter['chapter_url']))
            # print('=======存入章节成功===============')
            except sqlite3.IntegrityError as e:
                print('章节已经存在')

        self.db_conn.commit()

# ===================存入漫画内容表=================
        for i in item['content']:
            for j in i['img_list']:
                try:
                    self.db_cur.execute(
                        "INSERT INTO comicinfo_comicdetail(belongto_catalog_id,detail_content) VALUES (?,?)",
                        (item['book_id']+'/'+i['chapter_id'],j))
                except sqlite3.IntegrityError as e:
                    print('图片内容已经存在')

        print('=======存入内容成功===============')

        self.db_conn.commit()

    def close(self, spider):
        self.db_conn.close()
